package com.yzdbx.individualtask;

import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.util.ObjectUtil;
import cn.hutool.core.util.RandomUtil;
import com.yzdbx.utils.DbUtil;
import com.yzdbx.utils.GenUtil;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.time.LocalDate;
import java.time.ZoneId;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

/**
 * 完整的生产流程重新生成工具
 * 根据原有生成逻辑，按正确顺序删除和生成所有生产相关数据
 */
public class CompleteProductionFlowReGen {
    
    /**
     * 生产流程数据依赖关系：
     * 1. 销售订单 (salesOrders, xs02SalesOrderBreakdown)
     * 2. 生产计划 (sc01ProductionPlan) - 依赖销售订单
     * 3. 生产工单 (missionInformationSc02) - 依赖生产计划
     * 4. 生产领料单 (itsACollarList, checklistOfTheCollar) - 依赖生产工单
     * 5. 工艺任务 (sctechMissions) - 依赖生产工单
     * 6. 报工明细 (checklistSc05) - 依赖工艺任务
     * 7. 生产领料出库 (lists, ck02CheckoutFormCheckout) - 依赖生产领料单
     * 8. 成品入库 (ck03Listing, ck04ListOfEntriesListOfEntries) - 依赖生产工单
     */
    public static void main(String[] args) throws Exception {
        // 处理日期参数
        String startDate = "2025-01-21";
        String endDate = "2025-02-21";
        
        if (args.length >= 2) {
            startDate = args[0];
            endDate = args[1];
            System.out.println("指定生成日期范围: " + startDate + " 到 " + endDate);
        } else if (args.length == 1) {
            startDate = args[0];
            endDate = args[0];
            System.out.println("指定生成日期: " + startDate);
        } else {
            System.out.println("未指定日期，将重新生成所有生产流程数据");
        }
        
        // 初始化 GenUtil 的日期信息
        LocalDate currentDate = LocalDate.now();
        if (startDate != null) {
            currentDate = LocalDate.parse(startDate);
        }
        GenUtil.setCurDate(currentDate);
        GenUtil.setCurTimeStamp(new Timestamp(currentDate.atStartOfDay(ZoneId.systemDefault()).toInstant().toEpochMilli()));
        
        Connection connection = DbUtil.getConnection();
        boolean originalAutoCommit = true;
        
        try {
            originalAutoCommit = connection.getAutoCommit();
            connection.setAutoCommit(false);

            System.out.println("=== 开始完整的生产流程数据重新生成 ===");
            
            // 第一步：按依赖关系倒序删除所有生产相关数据
            deleteProductionData(connection, startDate, endDate);
            
            // 第二步：检查并删除可能遗漏的数据（仅在未指定日期范围时执行）
            if (startDate == null || endDate == null || startDate.equals("2025-01-21") && endDate.equals("2025-02-21")) {
                // 默认日期范围或未指定日期时，执行完整检查
                checkAndDeleteRemainingData(connection, startDate, endDate);
            } else {
                System.out.println("\n--- 跳过第二步检查（已按日期范围删除数据）---");
            }
            
            // 第三步：按依赖关系正序重新生成数据
            regenerateProductionData(connection, startDate, endDate);
            
            System.out.println("=== 生产流程数据重新生成完成 ===");
            
        } finally {
            try {
                connection.setAutoCommit(originalAutoCommit);
            } catch (SQLException ignore) {}
            DbUtil.closeConnection(connection);
        }
    }
    
    /**
     * 检查并删除可能遗漏的数据
     */
    private static void checkAndDeleteRemainingData(Connection connection, String startDate, String endDate) throws SQLException {
        System.out.println("\n--- 第二步：检查并删除可能遗漏的数据 ---");
        
        try (Statement statement = connection.createStatement()) {
            
            // 检查是否还有遗漏的数据
            System.out.println("检查是否还有遗漏的数据...");
            
            // 检查生产计划
            ResultSet rs = statement.executeQuery("SELECT COUNT(*) FROM sc01ProductionPlan");
            rs.next();
            int planCount = rs.getInt(1);
            if (planCount > 0) {
                System.out.println("发现 " + planCount + " 条生产计划数据，强制删除...");
                statement.executeUpdate("DELETE FROM sc01ProductionPlan");
            }
            
            // 检查生产工单
            rs = statement.executeQuery("SELECT COUNT(*) FROM missionInformationSc02");
            rs.next();
            int missionCount = rs.getInt(1);
            if (missionCount > 0) {
                System.out.println("发现 " + missionCount + " 条生产工单数据，强制删除...");
                statement.executeUpdate("DELETE FROM missionInformationSc02");
            }
            
            // 检查生产领料单
            rs = statement.executeQuery("SELECT COUNT(*) FROM itsACollarList");
            rs.next();
            int collarCount = rs.getInt(1);
            if (collarCount > 0) {
                System.out.println("发现 " + collarCount + " 条生产领料单数据，强制删除...");
                statement.executeUpdate("DELETE FROM itsACollarList");
            }
            
            // 检查生产领料单明细
            rs = statement.executeQuery("SELECT COUNT(*) FROM checklistOfTheCollar");
            rs.next();
            int collarDetailCount = rs.getInt(1);
            if (collarDetailCount > 0) {
                System.out.println("发现 " + collarDetailCount + " 条生产领料单明细数据，强制删除...");
                statement.executeUpdate("DELETE FROM checklistOfTheCollar");
            }
            
            // 检查工艺任务
            rs = statement.executeQuery("SELECT COUNT(*) FROM sctechMissions");
            rs.next();
            int techCount = rs.getInt(1);
            if (techCount > 0) {
                System.out.println("发现 " + techCount + " 条工艺任务数据，强制删除...");
                statement.executeUpdate("DELETE FROM sctechMissions");
            }
            
            // 检查报工明细
            rs = statement.executeQuery("SELECT COUNT(*) FROM checklistSc05");
            rs.next();
            int reportCount = rs.getInt(1);
            if (reportCount > 0) {
                System.out.println("发现 " + reportCount + " 条报工明细数据，强制删除...");
                statement.executeUpdate("DELETE FROM checklistSc05");
            }
            
            // 检查生产领料出库
            rs = statement.executeQuery("SELECT COUNT(*) FROM lists WHERE poolType = '领料出库'");
            rs.next();
            int outboundCount = rs.getInt(1);
            if (outboundCount > 0) {
                System.out.println("发现 " + outboundCount + " 条生产领料出库数据，强制删除...");
                statement.executeUpdate("DELETE FROM lists WHERE poolType = '领料出库'");
            }
            
            // 检查 lists 表中是否还有生产领料出库数据
            rs = statement.executeQuery("SELECT COUNT(*) FROM lists WHERE poolType = '领料出库'");
            rs.next();
            int remainingOutboundCount = rs.getInt(1);
            if (remainingOutboundCount > 0) {
                System.out.println("发现 " + remainingOutboundCount + " 条遗留的生产领料出库数据，强制删除...");
                statement.executeUpdate("DELETE FROM lists WHERE poolType = '领料出库'");
            }
            
            // 检查生产领料出库明细
            rs = statement.executeQuery("SELECT COUNT(*) FROM ck02CheckoutFormCheckout WHERE linkOutList IN (SELECT id FROM lists WHERE poolType = '领料出库')");
            rs.next();
            int outboundDetailCount = rs.getInt(1);
            if (outboundDetailCount > 0) {
                System.out.println("发现 " + outboundDetailCount + " 条生产领料出库明细数据，强制删除...");
                statement.executeUpdate("DELETE FROM ck02CheckoutFormCheckout WHERE linkOutList IN (SELECT id FROM lists WHERE poolType = '领料出库')");
            }
            
            // 检查成品检验 - 已移除，不删除现有成品检验数据
            System.out.println("跳过成品检验数据检查，保留现有数据...");
            
            // 检查成品入库
            rs = statement.executeQuery("SELECT COUNT(*) FROM ck03Listing WHERE documentType = '生产入库'");
            rs.next();
            int inboundCount = rs.getInt(1);
            if (inboundCount > 0) {
                System.out.println("发现 " + inboundCount + " 条成品入库数据，强制删除...");
                statement.executeUpdate("DELETE FROM ck03Listing WHERE documentType = '生产入库'");
            }
            
            // 检查成品入库明细
            rs = statement.executeQuery("SELECT COUNT(*) FROM ck04ListOfEntriesListOfEntries WHERE linkToList IN (SELECT id FROM ck03Listing WHERE documentType = '生产入库')");
            rs.next();
            int inboundDetailCount = rs.getInt(1);
            if (inboundDetailCount > 0) {
                System.out.println("发现 " + inboundDetailCount + " 条成品入库明细数据，强制删除...");
                statement.executeUpdate("DELETE FROM ck04ListOfEntriesListOfEntries WHERE linkToList IN (SELECT id FROM ck03Listing WHERE documentType = '生产入库')");
            }
            
            connection.commit();
            System.out.println("遗漏数据检查完成");
        }
    }
    
    /**
     * 按依赖关系倒序删除所有生产相关数据
     */
    private static void deleteProductionData(Connection connection, String startDate, String endDate) throws SQLException {
        System.out.println("\n--- 第一步：删除现有生产流程数据 ---");
        
        try (Statement statement = connection.createStatement()) {
            
            // 根据日期范围删除相关数据
            if (startDate != null && endDate != null) {
                System.out.println("删除 " + startDate + " 到 " + endDate + " 日期范围内的生产相关数据...");
            } else {
                System.out.println("删除所有生产相关数据...");
            }
            
            // 9. 删除成品入库数据 (最后生成，最先删除)
            System.out.println("删除成品入库数据...");
            String deleteInboundDetailSql;
            String deleteInboundMainSql;
            
            if (startDate != null && endDate != null) {
                if (startDate.equals(endDate)) {
                    deleteInboundDetailSql = "DELETE FROM ck04ListOfEntriesListOfEntries WHERE linkToList IN (SELECT id FROM ck03Listing WHERE documentType = '生产入库' AND DATE(repositoryDate) = ?)";
                    deleteInboundMainSql = "DELETE FROM ck03Listing WHERE documentType = '生产入库' AND DATE(repositoryDate) = ?";
                } else {
                    deleteInboundDetailSql = "DELETE FROM ck04ListOfEntriesListOfEntries WHERE linkToList IN (SELECT id FROM ck03Listing WHERE documentType = '生产入库' AND DATE(repositoryDate) BETWEEN ? AND ?)";
                    deleteInboundMainSql = "DELETE FROM ck03Listing WHERE documentType = '生产入库' AND DATE(repositoryDate) BETWEEN ? AND ?";
                }
            } else {
                deleteInboundDetailSql = "DELETE FROM ck04ListOfEntriesListOfEntries WHERE linkToList IN (SELECT id FROM ck03Listing WHERE documentType = '生产入库')";
                deleteInboundMainSql = "DELETE FROM ck03Listing WHERE documentType = '生产入库'";
            }
            
            int deletedInboundDetailCount;
            int deletedInboundMainCount;
            
            if (startDate != null && endDate != null) {
                try (PreparedStatement detailStmt = connection.prepareStatement(deleteInboundDetailSql);
                     PreparedStatement mainStmt = connection.prepareStatement(deleteInboundMainSql)) {
                    
                    if (startDate.equals(endDate)) {
                        detailStmt.setString(1, startDate);
                        mainStmt.setString(1, startDate);
                    } else {
                        detailStmt.setString(1, startDate);
                        detailStmt.setString(2, endDate);
                        mainStmt.setString(1, startDate);
                        mainStmt.setString(2, endDate);
                    }
                    
                    deletedInboundDetailCount = detailStmt.executeUpdate();
                    deletedInboundMainCount = mainStmt.executeUpdate();
                }
            } else {
                deletedInboundDetailCount = statement.executeUpdate(deleteInboundDetailSql);
                deletedInboundMainCount = statement.executeUpdate(deleteInboundMainSql);
            }
            
            System.out.println("删除了 " + deletedInboundDetailCount + " 条成品入库明细记录");
            System.out.println("删除了 " + deletedInboundMainCount + " 条成品入库主记录");
            
            // 8. 删除成品检验数据 - 已移除，不删除现有成品检验数据
            System.out.println("跳过成品检验数据删除，保留现有数据...");
            
            // 7. 删除生产领料出库数据
            System.out.println("删除生产领料出库数据...");
            String deleteOutboundDetailSql;
            String deleteOutboundMainSql;
            
            if (startDate != null && endDate != null) {
                if (startDate.equals(endDate)) {
                    deleteOutboundDetailSql = "DELETE FROM ck02CheckoutFormCheckout WHERE linkOutList IN (SELECT id FROM lists WHERE poolType = '领料出库' AND DATE(collectionDate) = ?)";
                    deleteOutboundMainSql = "DELETE FROM lists WHERE poolType = '领料出库' AND DATE(collectionDate) = ?";
                } else {
                    deleteOutboundDetailSql = "DELETE FROM ck02CheckoutFormCheckout WHERE linkOutList IN (SELECT id FROM lists WHERE poolType = '领料出库' AND DATE(collectionDate) BETWEEN ? AND ?)";
                    deleteOutboundMainSql = "DELETE FROM lists WHERE poolType = '领料出库' AND DATE(collectionDate) BETWEEN ? AND ?";
                }
            } else {
                deleteOutboundDetailSql = "DELETE FROM ck02CheckoutFormCheckout WHERE linkOutList IN (SELECT id FROM lists WHERE poolType = '领料出库')";
                deleteOutboundMainSql = "DELETE FROM lists WHERE poolType = '领料出库'";
            }
            
            int deletedOutboundDetailCount;
            int deletedOutboundMainCount;
            
            if (startDate != null && endDate != null) {
                try (PreparedStatement detailStmt = connection.prepareStatement(deleteOutboundDetailSql);
                     PreparedStatement mainStmt = connection.prepareStatement(deleteOutboundMainSql)) {
                    
                    if (startDate.equals(endDate)) {
                        detailStmt.setString(1, startDate);
                        mainStmt.setString(1, startDate);
                    } else {
                        detailStmt.setString(1, startDate);
                        detailStmt.setString(2, endDate);
                        mainStmt.setString(1, startDate);
                        mainStmt.setString(2, endDate);
                    }
                    
                    deletedOutboundDetailCount = detailStmt.executeUpdate();
                    deletedOutboundMainCount = mainStmt.executeUpdate();
                }
            } else {
                deletedOutboundDetailCount = statement.executeUpdate(deleteOutboundDetailSql);
                deletedOutboundMainCount = statement.executeUpdate(deleteOutboundMainSql);
            }
            
            System.out.println("删除了 " + deletedOutboundDetailCount + " 条生产领料出库明细记录");
            System.out.println("删除了 " + deletedOutboundMainCount + " 条生产领料出库主记录");
            
            // 6. 删除报工明细数据
            System.out.println("删除报工明细数据...");
            String deleteReportSql;
            if (startDate != null && endDate != null) {
                if (startDate.equals(endDate)) {
                    deleteReportSql = "DELETE FROM checklistSc05 WHERE linkProcessTasks IN (SELECT id FROM sctechMissions WHERE associatedTasks IN (SELECT id FROM missionInformationSc02 WHERE DATE(otherOrganiser) = ?))";
                } else {
                    deleteReportSql = "DELETE FROM checklistSc05 WHERE linkProcessTasks IN (SELECT id FROM sctechMissions WHERE associatedTasks IN (SELECT id FROM missionInformationSc02 WHERE DATE(otherOrganiser) BETWEEN ? AND ?))";
                }
            } else {
                deleteReportSql = "DELETE FROM checklistSc05";
            }
            
            int deletedReportCount;
            if (startDate != null && endDate != null) {
                try (PreparedStatement reportStmt = connection.prepareStatement(deleteReportSql)) {
                    if (startDate.equals(endDate)) {
                        reportStmt.setString(1, startDate);
                    } else {
                        reportStmt.setString(1, startDate);
                        reportStmt.setString(2, endDate);
                    }
                    deletedReportCount = reportStmt.executeUpdate();
                }
            } else {
                deletedReportCount = statement.executeUpdate(deleteReportSql);
            }
            System.out.println("删除了 " + deletedReportCount + " 条报工明细记录");
            
            // 5. 删除工艺任务数据
            System.out.println("删除工艺任务数据...");
            String deleteTechSql;
            if (startDate != null && endDate != null) {
                if (startDate.equals(endDate)) {
                    deleteTechSql = "DELETE FROM sctechMissions WHERE associatedTasks IN (SELECT id FROM missionInformationSc02 WHERE DATE(otherOrganiser) = ?)";
                } else {
                    deleteTechSql = "DELETE FROM sctechMissions WHERE associatedTasks IN (SELECT id FROM missionInformationSc02 WHERE DATE(otherOrganiser) BETWEEN ? AND ?)";
                }
            } else {
                deleteTechSql = "DELETE FROM sctechMissions";
            }
            
            int deletedTechCount;
            if (startDate != null && endDate != null) {
                try (PreparedStatement techStmt = connection.prepareStatement(deleteTechSql)) {
                    if (startDate.equals(endDate)) {
                        techStmt.setString(1, startDate);
                    } else {
                        techStmt.setString(1, startDate);
                        techStmt.setString(2, endDate);
                    }
                    deletedTechCount = techStmt.executeUpdate();
                }
            } else {
                deletedTechCount = statement.executeUpdate(deleteTechSql);
            }
            System.out.println("删除了 " + deletedTechCount + " 条工艺任务记录");
            
            // 4. 删除生产领料单明细数据
            System.out.println("删除生产领料单明细数据...");
            String deleteCollarDetailSql;
            if (startDate != null && endDate != null) {
                if (startDate.equals(endDate)) {
                    deleteCollarDetailSql = "DELETE FROM checklistOfTheCollar WHERE f8d0 IN (SELECT id FROM itsACollarList WHERE productionTasks IN (SELECT id FROM missionInformationSc02 WHERE DATE(otherOrganiser) = ?))";
                } else {
                    deleteCollarDetailSql = "DELETE FROM checklistOfTheCollar WHERE f8d0 IN (SELECT id FROM itsACollarList WHERE productionTasks IN (SELECT id FROM missionInformationSc02 WHERE DATE(otherOrganiser) BETWEEN ? AND ?))";
                }
            } else {
                deleteCollarDetailSql = "DELETE FROM checklistOfTheCollar";
            }
            
            int deletedCollarDetailCount;
            if (startDate != null && endDate != null) {
                try (PreparedStatement collarDetailStmt = connection.prepareStatement(deleteCollarDetailSql)) {
                    if (startDate.equals(endDate)) {
                        collarDetailStmt.setString(1, startDate);
                    } else {
                        collarDetailStmt.setString(1, startDate);
                        collarDetailStmt.setString(2, endDate);
                    }
                    deletedCollarDetailCount = collarDetailStmt.executeUpdate();
                }
            } else {
                deletedCollarDetailCount = statement.executeUpdate(deleteCollarDetailSql);
            }
            System.out.println("删除了 " + deletedCollarDetailCount + " 条领料单明细记录");
            
            // 3. 删除生产领料单数据
            System.out.println("删除生产领料单数据...");
            String deleteCollarSql;
            if (startDate != null && endDate != null) {
                if (startDate.equals(endDate)) {
                    deleteCollarSql = "DELETE FROM itsACollarList WHERE productionTasks IN (SELECT id FROM missionInformationSc02 WHERE DATE(otherOrganiser) = ?)";
                } else {
                    deleteCollarSql = "DELETE FROM itsACollarList WHERE productionTasks IN (SELECT id FROM missionInformationSc02 WHERE DATE(otherOrganiser) BETWEEN ? AND ?)";
                }
            } else {
                deleteCollarSql = "DELETE FROM itsACollarList";
            }
            
            int deletedCollarCount;
            if (startDate != null && endDate != null) {
                try (PreparedStatement collarStmt = connection.prepareStatement(deleteCollarSql)) {
                    if (startDate.equals(endDate)) {
                        collarStmt.setString(1, startDate);
                    } else {
                        collarStmt.setString(1, startDate);
                        collarStmt.setString(2, endDate);
                    }
                    deletedCollarCount = collarStmt.executeUpdate();
                }
            } else {
                deletedCollarCount = statement.executeUpdate(deleteCollarSql);
            }
            System.out.println("删除了 " + deletedCollarCount + " 条领料单记录");
            
            // 2. 删除生产工单数据
            System.out.println("删除生产工单数据...");
            String deleteMissionSql;
            if (startDate != null && endDate != null) {
                if (startDate.equals(endDate)) {
                    deleteMissionSql = "DELETE FROM missionInformationSc02 WHERE DATE(otherOrganiser) = ?";
                } else {
                    deleteMissionSql = "DELETE FROM missionInformationSc02 WHERE DATE(otherOrganiser) BETWEEN ? AND ?";
                }
            } else {
                deleteMissionSql = "DELETE FROM missionInformationSc02";
            }
            
            int deletedMissionCount;
            if (startDate != null && endDate != null) {
                try (PreparedStatement missionStmt = connection.prepareStatement(deleteMissionSql)) {
                    if (startDate.equals(endDate)) {
                        missionStmt.setString(1, startDate);
                    } else {
                        missionStmt.setString(1, startDate);
                        missionStmt.setString(2, endDate);
                    }
                    deletedMissionCount = missionStmt.executeUpdate();
                }
            } else {
                deletedMissionCount = statement.executeUpdate(deleteMissionSql);
            }
            System.out.println("删除了 " + deletedMissionCount + " 条生产工单记录");
            
            // 1. 删除生产计划数据
            System.out.println("删除生产计划数据...");
            String deletePlanSql;
            if (startDate != null && endDate != null) {
                if (startDate.equals(endDate)) {
                    deletePlanSql = "DELETE FROM sc01ProductionPlan WHERE DATE(dateOfDelivery) = ?";
                } else {
                    deletePlanSql = "DELETE FROM sc01ProductionPlan WHERE DATE(dateOfDelivery) BETWEEN ? AND ?";
                }
            } else {
                deletePlanSql = "DELETE FROM sc01ProductionPlan";
            }
            
            int deletedPlanCount;
            if (startDate != null && endDate != null) {
                try (PreparedStatement planStmt = connection.prepareStatement(deletePlanSql)) {
                    if (startDate.equals(endDate)) {
                        planStmt.setString(1, startDate);
                    } else {
                        planStmt.setString(1, startDate);
                        planStmt.setString(2, endDate);
                    }
                    deletedPlanCount = planStmt.executeUpdate();
                }
            } else {
                deletedPlanCount = statement.executeUpdate(deletePlanSql);
            }
            System.out.println("删除了 " + deletedPlanCount + " 条生产计划记录");
            
            connection.commit();
            System.out.println("所有生产流程数据删除完成");
        }
    }
    
    /**
     * 按依赖关系正序重新生成数据
     */
    private static void regenerateProductionData(Connection connection, String startDate, String endDate) throws Exception {
        System.out.println("\n--- 第二步：重新生成生产流程数据 ---");
        
        // 1. 重新生成生产计划
        System.out.println("\n1. 生成生产计划数据...");
        regenerateProductionPlans(connection, startDate, endDate);
        
        // 2. 重新生成生产工单及相关数据
        System.out.println("\n2. 生成生产工单及相关数据...");
        regenerateProductionOrders(connection, startDate, endDate);
        
        // 3. 重新生成生产领料出库
        System.out.println("\n3. 生成生产领料出库数据...");
        regenerateProductionOutbound(connection, startDate, endDate);
        
        // 4. 重新生成成品入库
        System.out.println("\n4. 生成成品入库数据...");
        regenerateFinishedProductInbound(connection, startDate, endDate);
    }
    
    /**
     * 重新生成生产计划
     */
    private static void regenerateProductionPlans(Connection connection, String startDate, String endDate) throws Exception {
        // 查询销售订单ID（根据日期范围过滤）
        List<Integer> orderIds = new ArrayList<>();
        String orderQuery;
        PreparedStatement stmt;
        
        if (startDate != null && endDate != null) {
            if (startDate.equals(endDate)) {
                orderQuery = "SELECT id FROM salesOrders WHERE DATE(dateOfOrder) = ?";
                stmt = connection.prepareStatement(orderQuery);
                stmt.setString(1, startDate);
            } else {
                orderQuery = "SELECT id FROM salesOrders WHERE DATE(dateOfOrder) BETWEEN ? AND ?";
                stmt = connection.prepareStatement(orderQuery);
                stmt.setString(1, startDate);
                stmt.setString(2, endDate);
            }
        } else {
            orderQuery = "SELECT id FROM salesOrders";
            stmt = connection.prepareStatement(orderQuery);
        }
        
        ResultSet rs = stmt.executeQuery();
        while (rs.next()) {
            orderIds.add(rs.getInt("id"));
        }
        rs.close();
        stmt.close();

        if (CollectionUtil.isEmpty(orderIds)) {
            if (startDate != null && endDate != null) {
                if (startDate.equals(endDate)) {
                    System.out.println("没有找到 " + startDate + " 日期的销售订单数据，无法生成生产计划数据！");
                } else {
                    System.out.println("没有找到 " + startDate + " 到 " + endDate + " 日期范围的销售订单数据，无法生成生产计划数据！");
                }
            } else {
                System.out.println("没有找到销售订单数据，无法生成生产计划数据！");
            }
            return;
        }

        System.out.println("找到 " + orderIds.size() + " 个销售订单，准备生成对应的生产计划数据");

        int maxPlanId = getMaxId(connection, "sc01ProductionPlan");
        int planCount = 0;
        
        // 按日期分组的计划单号计数器
        Map<String, Integer> planDateCounterMap = new HashMap<>();

        // 准备批量插入语句
        String planSql = "insert into sc01ProductionPlan (id,scheduleNumber,clientName,dateOfDelivery,number,numberCompleted,productInformation,whetherOrNotArrangementsAreInPla,guanLianXiaoShouDingDan,synchronisingFolderFailedSS,created,modifyer,modified) values (?,?,?,?,?,?,?,?,?,?,?,?,?)";

        try (PreparedStatement planStatement = connection.prepareStatement(planSql)) {

            for (Integer orderId : orderIds) {
                // 查询销售订单
                Map<String, Object> saleOrder = DbUtil.getById(connection, "salesOrders", orderId);
                if (saleOrder == null) continue;
                
                // 获取销售订单的订单日期
                java.sql.Date orderDate = (java.sql.Date) saleOrder.get("dateOfOrder");
                Timestamp orderTimestamp = new Timestamp(orderDate.getTime());
                
                // 设置当前日期为订单日期，确保计划单号生成正确
                GenUtil.setCurDate(orderDate.toLocalDate());
                GenUtil.setCurTimeStamp(orderTimestamp);
                
                // 查询销售订单明细
                List<LinkedHashMap<String, Object>> details;
                try (PreparedStatement statement = connection.prepareStatement("select * from xs02SalesOrderBreakdown where associatedSalesOrders = ?")) {
                    statement.setInt(1, orderId);
                    ResultSet resultSet = statement.executeQuery();
                    details = DbUtil.resultSetToList(resultSet);
                }

                if (details.isEmpty()) {
                    System.out.println("销售订单 " + orderId + " 没有明细数据，跳过");
                    continue;
                }

                for (LinkedHashMap<String, Object> detail : details) {
                    // 按日期分组的计划单号生成逻辑
                    String dateKey = orderDate.toString();
                    int currentCount = planDateCounterMap.getOrDefault(dateKey, 0) + 1;
                    planDateCounterMap.put(dateKey, currentCount);
                    
                    // 生成计划单号
                    String scheduleNumber = GenUtil.getNo("JH", currentCount);
                    System.out.println("正在处理第 " + (++planCount) + " 个生产计划，计划单号: " + scheduleNumber + ", 日期: " + orderDate + ", 销售订单ID: " + orderId);

                    planStatement.setInt(1, ++maxPlanId);
                    planStatement.setString(2, scheduleNumber);
                    planStatement.setInt(3, (int) saleOrder.get("clientInformation"));
                    planStatement.setDate(4, orderDate);
                    planStatement.setBigDecimal(5, (BigDecimal) detail.get("number"));
                    planStatement.setBigDecimal(6, (BigDecimal) detail.get("number"));
                    planStatement.setInt(7, (int) detail.get("productInformation"));
                    planStatement.setString(8, "存储内容2");
                    planStatement.setInt(9, orderId);
                    planStatement.setString(10, saleOrder.get("synchronisingFolderFailedSS").toString());
                    planStatement.setTimestamp(11, orderTimestamp);
                    planStatement.setString(12, saleOrder.get("synchronisingFolderFailedSS").toString());
                    planStatement.setTimestamp(13, orderTimestamp);
                    planStatement.addBatch();
                }
            }
            
            planStatement.executeBatch();
            connection.commit();
        }
        
        System.out.println("生产计划数据生成完成，共生成 " + planCount + " 条记录");
    }
    
    /**
     * 重新生成生产工单及相关数据
     */
    private static void regenerateProductionOrders(Connection connection, String startDate, String endDate) throws Exception {
        // 查询生产计划ID（根据日期范围过滤）
        List<Integer> planIds = new ArrayList<>();
        String planQuery;
        PreparedStatement stmt;
        
        if (startDate != null && endDate != null) {
            if (startDate.equals(endDate)) {
                planQuery = "SELECT id FROM sc01ProductionPlan WHERE DATE(dateOfDelivery) = ?";
                stmt = connection.prepareStatement(planQuery);
                stmt.setString(1, startDate);
            } else {
                planQuery = "SELECT id FROM sc01ProductionPlan WHERE DATE(dateOfDelivery) BETWEEN ? AND ?";
                stmt = connection.prepareStatement(planQuery);
                stmt.setString(1, startDate);
                stmt.setString(2, endDate);
            }
        } else {
            planQuery = "SELECT id FROM sc01ProductionPlan";
            stmt = connection.prepareStatement(planQuery);
        }
        
        ResultSet rs = stmt.executeQuery();
        while (rs.next()) {
            planIds.add(rs.getInt("id"));
        }
        rs.close();
        stmt.close();

        if (CollectionUtil.isEmpty(planIds)) {
            if (startDate != null && endDate != null) {
                if (startDate.equals(endDate)) {
                    System.out.println("没有找到 " + startDate + " 日期的生产计划数据，无法生成生产工单数据！");
                } else {
                    System.out.println("没有找到 " + startDate + " 到 " + endDate + " 日期范围的生产计划数据，无法生成生产工单数据！");
                }
            } else {
                System.out.println("没有找到生产计划数据，无法生成生产工单数据！");
            }
            return;
        }

        System.out.println("找到 " + planIds.size() + " 个生产计划，准备生成对应的生产工单数据");

        List<Integer> orderIds = new ArrayList<>(planIds.size());
        List<Integer> lldIds = new ArrayList<>(planIds.size());
        int batchCount = 0;
        final int BATCH_SIZE = 3;
        
        // 查询员工信息
        List<String> members;
        try (PreparedStatement statement1 = connection.prepareStatement("select * from corp_member")) {
            members = DbUtil.resultSetToList(statement1.executeQuery()).stream()
                    .map(map -> map.get("memberId").toString()).toList();
        }

        int rwId = getMaxId(connection, "missionInformationSc02");
        
        // 按日期分组的单号计数器，确保每天从0001开始
        Map<String, Integer> rwDateCounterMap = new HashMap<>();
        Map<String, Integer> llDateCounterMap = new HashMap<>();
        Map<String, Integer> gyDateCounterMap = new HashMap<>();
        Map<String, Integer> bgDateCounterMap = new HashMap<>();

        //查询工艺价格
        Map<String, BigDecimal> collect;
        try (PreparedStatement preparedStatement = connection.prepareStatement("select * from processBasicTables")) {
            collect = DbUtil.resultSetToList(preparedStatement.executeQuery()).stream()
                    .collect(Collectors.toMap(cur -> cur.get("processName").toString(), 
                            cur -> ((BigDecimal) cur.get("unitPrice"))));
        }

        int lldId = getMaxId(connection, "itsACollarList");
        int lldxqId = getMaxId(connection, "checklistOfTheCollar");
        int gyId = getMaxId(connection, "sctechMissions");
        int bgId = getMaxId(connection, "checklistSc05");

        // 准备批量插入语句
        String missionSql = "insert into missionInformationSc02(id,missionStream,productName,otherOrganiser,endOfSchedule,dateOfDelivery,numberOfTasks,numberCompleted,officerincharge,productionPlan,taskStatus,typeOfWorksheet,synchronisingFolderFailedSS,created,actualEndDate,f271) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
        String collarSql = "insert into itsACollarList (id,theCollarNumber,associatedProducts,productionTasks,f775,theLeadman,associatedOrders,numberToBeProduced) values (?,?,?,?,?,?,?,?)";
        String collarDetailSql = "insert into checklistOfTheCollar (id,f8d0,materialInformation,number)values (?,?,?,?)";
        String techMissionSql = "insert into sctechMissions (id,craftFlowNumber,associatedTasks,processName,serialNumber,productInformation,dateOfDelivery,plannedEndDate,numberOfTasks,numberCompleted,mandatedPersons,routineStatus)values (?,?,?,?,?,?,?,?,?,?,?,?)";
        String reportSql = "insert into checklistSc05 (id,wellCallTheFactoryStreamer,linkProcessTasks,associatedProductionTasks,productInformation,processName,dateOfReport,numberOfTasks,numberOfCompleted,numberOfReporters,numberOfEndoflifeWarranties,reportingStaff,auditStatus,unitPrice,synchronisingFolderFailedSS,created,modifyer,modified) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
        String updateCollarSql = "update itsACollarList set totalNumber = ? where id = ?";

        try (PreparedStatement missionStatement = connection.prepareStatement(missionSql);
             PreparedStatement collarStatement = connection.prepareStatement(collarSql);
             PreparedStatement collarDetailStatement = connection.prepareStatement(collarDetailSql);
             PreparedStatement techMissionStatement = connection.prepareStatement(techMissionSql);
             PreparedStatement reportStatement = connection.prepareStatement(reportSql);
             PreparedStatement updateCollarStatement = connection.prepareStatement(updateCollarSql)) {

            for (Integer planId : planIds) {
                Map<String, Object> productPlan = DbUtil.getById(connection, "sc01ProductionPlan", planId);
                if (productPlan == null) continue;
                
                // 获取生产计划的交付日期
                java.sql.Date deliveryDate = (java.sql.Date) productPlan.get("dateOfDelivery");
                Timestamp planTimestamp = (Timestamp) productPlan.get("created");
                
                // 设置当前日期为交付日期，确保单号生成正确
                GenUtil.setCurDate(deliveryDate.toLocalDate());
                GenUtil.setCurTimeStamp(planTimestamp);
                
                // 按日期分组的单号生成逻辑
                String dateKey = deliveryDate.toString();
                int rwCurrentCount = rwDateCounterMap.getOrDefault(dateKey, 0) + 1;
                rwDateCounterMap.put(dateKey, rwCurrentCount);
                
                int llCurrentCount = llDateCounterMap.getOrDefault(dateKey, 0) + 1;
                llDateCounterMap.put(dateKey, llCurrentCount);
                
                System.out.println("正在处理第 " + (orderIds.size() + 1) + " 个生产工单，工单号: " + GenUtil.getNo("RW", rwCurrentCount) + ", 领料单号: " + GenUtil.getNo("LL", llCurrentCount) + ", 日期: " + deliveryDate + ", 生产计划ID: " + planId);

                // 创建生产工单
                missionStatement.setInt(1, ++rwId);
                orderIds.add(rwId);
                missionStatement.setString(2, GenUtil.getNo("RW", rwCurrentCount));
                missionStatement.setInt(3, ((int) productPlan.get("productInformation")));
                missionStatement.setDate(4, deliveryDate);
                missionStatement.setDate(5, deliveryDate);
                missionStatement.setDate(6, deliveryDate);
                missionStatement.setBigDecimal(7, ((BigDecimal) productPlan.get("number")));
                missionStatement.setBigDecimal(8, ((BigDecimal) productPlan.get("number")));
                missionStatement.setString(9, productPlan.get("synchronisingFolderFailedSS").toString());
                missionStatement.setInt(10, planId);
                missionStatement.setString(11, "未开始");
                missionStatement.setString(12, "存储内容2");
                missionStatement.setString(13, productPlan.get("synchronisingFolderFailedSS").toString());
                missionStatement.setTimestamp(14, planTimestamp);
                missionStatement.setDate(15, deliveryDate);
                missionStatement.setString(16, "已排程");
                missionStatement.addBatch();

                //生产工单领料单
                collarStatement.setInt(1, ++lldId);
                lldIds.add(lldId);
                collarStatement.setString(2, GenUtil.getNo("LL", llCurrentCount));
                collarStatement.setInt(3, ((int) productPlan.get("productInformation")));
                collarStatement.setInt(4, rwId);
                collarStatement.setDate(5, deliveryDate);
                collarStatement.setString(6, productPlan.get("synchronisingFolderFailedSS").toString());
                collarStatement.setInt(7, planId);
                collarStatement.setBigDecimal(8, ((BigDecimal) productPlan.get("number")));
                collarStatement.addBatch();

                //生产领料单明细
                List<LinkedHashMap<String, Object>> lldList;
                try (PreparedStatement statement = connection.prepareStatement("select * from productBom where rawMaterials = ?")) {
                    statement.setInt(1, ((int) productPlan.get("productInformation")));
                    lldList = DbUtil.resultSetToList(statement.executeQuery());
                }

                BigDecimal total = BigDecimal.ZERO;
                for (LinkedHashMap<String, Object> lld : lldList) {
                    collarDetailStatement.setInt(1, ++lldxqId);
                    collarDetailStatement.setInt(2, lldId);
                    collarDetailStatement.setInt(3, ((int) lld.get("rawMaterials1")));
                    BigDecimal quantity = ((BigDecimal) lld.get("number")).multiply(((BigDecimal) productPlan.get("number"))).setScale(2, BigDecimal.ROUND_HALF_UP);
                    collarDetailStatement.setBigDecimal(4, quantity);
                    collarDetailStatement.addBatch();
                    total = total.add(quantity);
                }

                //更新领料单总数量
                updateCollarStatement.setBigDecimal(1, total);
                updateCollarStatement.setInt(2, lldId);
                updateCollarStatement.addBatch();

                //查询产品工艺路线
                List<LinkedHashMap<String, Object>> gylxList;
                try (PreparedStatement statement = connection.prepareStatement("select * from gy0501ProcessProcessSubtable where associatedProductLibrary = ? order by id asc")) {
                    statement.setInt(1, ((int) productPlan.get("productInformation")));
                    gylxList = DbUtil.resultSetToList(statement.executeQuery());
                }

                for (LinkedHashMap<String, Object> gylx : gylxList) {
                    // 按日期分组的工艺任务和报工单号生成逻辑
                    int gyCurrentCount = gyDateCounterMap.getOrDefault(dateKey, 0) + 1;
                    gyDateCounterMap.put(dateKey, gyCurrentCount);
                    
                    int bgCurrentCount = bgDateCounterMap.getOrDefault(dateKey, 0) + 1;
                    bgDateCounterMap.put(dateKey, bgCurrentCount);
                    
                    //工单生成工艺任务
                    techMissionStatement.setInt(1, ++gyId);
                    techMissionStatement.setString(2, GenUtil.getNo("GY", gyCurrentCount));
                    techMissionStatement.setInt(3, rwId);
                    techMissionStatement.setString(4, gylx.get("f7ff").toString());
                    techMissionStatement.setInt(5, ((int) gylx.get("id")));
                    techMissionStatement.setInt(6, ((int) productPlan.get("productInformation")));
                    techMissionStatement.setDate(7, deliveryDate);
                    techMissionStatement.setDate(8, deliveryDate);
                    techMissionStatement.setBigDecimal(9, ((BigDecimal) productPlan.get("number")));
                    techMissionStatement.setBigDecimal(10, ((BigDecimal) productPlan.get("number")));
                    techMissionStatement.setString(11, productPlan.get("synchronisingFolderFailedSS").toString());
                    techMissionStatement.setString(12, "存储内容2");
                    techMissionStatement.addBatch();

                    //生成报工明细
                    reportStatement.setInt(1, ++bgId);
                    reportStatement.setString(2, GenUtil.getNo("BG", bgCurrentCount));
                    reportStatement.setInt(3, gyId);
                    reportStatement.setInt(4, rwId);
                    reportStatement.setInt(5, ((int) productPlan.get("productInformation")));
                    reportStatement.setString(6, gylx.get("f7ff").toString());
                    reportStatement.setDate(7, deliveryDate);
                    reportStatement.setBigDecimal(8, ((BigDecimal) productPlan.get("number")));
                    //随机完成数量
                    BigDecimal bigDecimal = RandomUtil.randomBigDecimal(BigDecimal.ONE, ((BigDecimal) productPlan.get("number")));
                    reportStatement.setBigDecimal(9, bigDecimal);
                    reportStatement.setBigDecimal(10, bigDecimal);
                    reportStatement.setBigDecimal(11, BigDecimal.ZERO);
                    reportStatement.setString(12, RandomUtil.randomEle(members));
                    reportStatement.setString(13, "已审核");
                    reportStatement.setBigDecimal(14, collect.get(gylx.get("f7ff").toString()));
                    reportStatement.setString(15, productPlan.get("synchronisingFolderFailedSS").toString());
                    reportStatement.setTimestamp(16, planTimestamp);
                    reportStatement.setString(17, productPlan.get("synchronisingFolderFailedSS").toString());
                    reportStatement.setTimestamp(18, planTimestamp);
                    reportStatement.addBatch();
                }

                batchCount++;

                // 每BATCH_SIZE条记录执行一次批量插入
                if (batchCount >= BATCH_SIZE) {
                    missionStatement.executeBatch();
                    collarStatement.executeBatch();
                    collarDetailStatement.executeBatch();
                    updateCollarStatement.executeBatch();
                    techMissionStatement.executeBatch();
                    reportStatement.executeBatch();
                    connection.commit();
                    batchCount = 0;
                }
            }

            // 执行剩余的批量插入
            if (batchCount > 0) {
                missionStatement.executeBatch();
                collarStatement.executeBatch();
                collarDetailStatement.executeBatch();
                updateCollarStatement.executeBatch();
                techMissionStatement.executeBatch();
                reportStatement.executeBatch();
                connection.commit();
            }
        }
        
        System.out.println("生产工单及相关数据生成完成，共生成 " + orderIds.size() + " 条生产工单记录");
    }
    
    /**
     * 重新生成生产领料出库（根据原有LLOutRunnable逻辑）
     */
    private static void regenerateProductionOutbound(Connection connection, String startDate, String endDate) throws Exception {
        // 查询生产领料单ID（根据日期范围过滤）
        List<Integer> lldIds = new ArrayList<>();
        String lldQuery;
        PreparedStatement stmt;
        
        if (startDate != null && endDate != null) {
            if (startDate.equals(endDate)) {
                lldQuery = "SELECT id FROM itsACollarList WHERE DATE(f775) = ?";
                stmt = connection.prepareStatement(lldQuery);
                stmt.setString(1, startDate);
            } else {
                lldQuery = "SELECT id FROM itsACollarList WHERE DATE(f775) BETWEEN ? AND ?";
                stmt = connection.prepareStatement(lldQuery);
                stmt.setString(1, startDate);
                stmt.setString(2, endDate);
            }
        } else {
            lldQuery = "SELECT id FROM itsACollarList";
            stmt = connection.prepareStatement(lldQuery);
        }
        
        ResultSet rs = stmt.executeQuery();
        while (rs.next()) {
            lldIds.add(rs.getInt("id"));
        }
        rs.close();
        stmt.close();

        if (CollectionUtil.isEmpty(lldIds)) {
            System.out.println("没有找到生产领料单数据，无法生成生产领料出库数据！");
            return;
        }

        System.out.println("找到 " + lldIds.size() + " 个生产领料单，准备生成对应的生产领料出库数据");

        try {
            int ckId = getMaxId(connection, "lists");
            int ckDetailId = getMaxId(connection, "ck02CheckoutFormCheckout");
            List<LinkedHashMap<String, Object>> sc01ProductionPlans = DbUtil.resultSetToList(connection.prepareStatement("select * from sc01ProductionPlan").executeQuery());
            
            int outboundCount = 0;
            // 按天维护最大号
            Map<String, Long> dateToCurrentNo = new HashMap<>();
            for (Integer lldId : lldIds) {
                Map<String, Object> lld = DbUtil.getById(connection, "itsACollarList", lldId);
                if (lld == null) continue;

                // 获取领料单的日期
                Object dateObj = lld.get("f775");
                java.sql.Date sqlDate = getSqlDate(dateObj);
                String dateStr = sqlDate.toString().replace("-", "");

                // 设置当前日期为领料单日期，确保单号生成正确
                GenUtil.setCurDate(sqlDate.toLocalDate());

                // 初始化当天最大号
                if (!dateToCurrentNo.containsKey(dateStr)) {
                    long maxNo = 0L;
                    try (PreparedStatement checkStmt = connection.prepareStatement("SELECT cashiersNumber FROM lists WHERE cashiersNumber LIKE ? ORDER BY cashiersNumber DESC LIMIT 1")) {
                        checkStmt.setString(1, "CK" + dateStr + "%");
                        ResultSet checkRs = checkStmt.executeQuery();
                        if (checkRs.next()) {
                            String maxCashiersNumber = checkRs.getString("cashiersNumber");
                            try {
                                String numberPart = maxCashiersNumber.substring(10); // 从第11位开始是序号
                                maxNo = Long.parseLong(numberPart);
                            } catch (NumberFormatException e) {
                                // 忽略无法解析的单号
                            }
                        }
                    }
                    dateToCurrentNo.put(dateStr, maxNo);
                }
                long currentNo = dateToCurrentNo.get(dateStr) + 1;
                dateToCurrentNo.put(dateStr, currentNo);
                String outboundNumber = "CK" + dateStr + String.format("%04d", currentNo);

                // 创建出库单
                PreparedStatement statement = connection.prepareStatement("insert into lists(id,cashiersNumber,poolType,collectionDate,operator,clientInformation,associatedOrders)values (?,?,?,?,?,?,?)");
                statement.setInt(1, ++ckId);
                statement.setString(2, outboundNumber);
                statement.setString(3, "领料出库");
                statement.setDate(4, sqlDate);
                statement.setString(5, lld.get("theLeadman").toString());
                int associatedOrders = (int) lld.get("associatedOrders");
                Map<String, Object> productionPlan = sc01ProductionPlans.stream().filter(map -> ObjectUtil.equal(map.get("id"), associatedOrders)).findFirst().get();
                statement.setInt(6, ((int) productionPlan.get("clientName")));
                statement.setInt(7, associatedOrders);
                statement.execute();

                // 创建出库明细
                statement = connection.prepareStatement("select * from checklistOfTheCollar where f8d0 = ?");
                statement.setInt(1, lldId);
                List<LinkedHashMap<String, Object>> lldDetailList = DbUtil.resultSetToList(statement.executeQuery());
                for (LinkedHashMap<String, Object> lldDetail : lldDetailList) {
                    statement = connection.prepareStatement("insert into ck02CheckoutFormCheckout(id,productInformation,numberOfReleases,inventoryUnitPrice,linkOutList,synchronisingFolderFailedSS,modifyer,created,modified)values (?,?,?,?,?,?,?,?,?)");
                    statement.setInt(1, ++ckDetailId);
                    int materialInformation = Integer.parseInt(lldDetail.get("materialInformation").toString());
                    statement.setInt(2, materialInformation);
                    BigDecimal number = (BigDecimal) lldDetail.get("number");
                    statement.setBigDecimal(3, number);
                    statement.setBigDecimal(4, RandomUtil.randomBigDecimal(BigDecimal.ONE, BigDecimal.valueOf(20)));
                    statement.setInt(5, ckId);
                    statement.setString(6, lld.get("theLeadman").toString());
                    statement.setString(7, lld.get("theLeadman").toString());
                    statement.setDate(8, sqlDate);
                    statement.setDate(9, sqlDate);
                    statement.execute();
                }
                
                outboundCount++;
                System.out.println("正在处理第 " + outboundCount + " 个生产领料出库，出库单号: " + outboundNumber + ", 日期: " + sqlDate + ", 领料单ID: " + lldId);
            }
            
            connection.commit();
        } catch (Exception ex) {
            throw new RuntimeException(ex);
        }
        
        System.out.println("生产领料出库数据生成完成，共生成 " + lldIds.size() + " 条记录");
    }
    

    
    /**
     * 重新生成成品入库（根据原有FinishProductIntoRunnable逻辑）
     */
    private static void regenerateFinishedProductInbound(Connection connection, String startDate, String endDate) throws Exception {
        // 查询生产工单ID（根据日期范围过滤）
        List<Integer> orderIds = new ArrayList<>();
        String orderQuery;
        PreparedStatement stmt;
        
        if (startDate != null && endDate != null) {
            if (startDate.equals(endDate)) {
                orderQuery = "SELECT id FROM missionInformationSc02 WHERE DATE(otherOrganiser) = ?";
                stmt = connection.prepareStatement(orderQuery);
                stmt.setString(1, startDate);
            } else {
                orderQuery = "SELECT id FROM missionInformationSc02 WHERE DATE(otherOrganiser) BETWEEN ? AND ?";
                stmt = connection.prepareStatement(orderQuery);
                stmt.setString(1, startDate);
                stmt.setString(2, endDate);
            }
        } else {
            orderQuery = "SELECT id FROM missionInformationSc02";
            stmt = connection.prepareStatement(orderQuery);
        }
        
        ResultSet rs = stmt.executeQuery();
        while (rs.next()) {
            orderIds.add(rs.getInt("id"));
        }
        rs.close();
        stmt.close();

        if (CollectionUtil.isEmpty(orderIds)) {
            System.out.println("没有找到生产工单数据，无法生成成品入库数据！");
            return;
        }

        System.out.println("找到 " + orderIds.size() + " 个生产工单，准备生成对应的成品入库数据");

        try {
            int maxId = getMaxId(connection, "ck03Listing");
            int maxIdDetail = getMaxId(connection, "ck04ListOfEntriesListOfEntries");
            int inboundCount = 0;
            
            // 查询当天已有的最大入库单号，避免重复
            long currentInboundNumber = 0L;
            for (Integer orderId : orderIds) {
                Map<String, Object> order = DbUtil.getById(connection, "missionInformationSc02", orderId);
                if (order == null) continue;
                
                // 获取生产工单的交付日期
                java.sql.Date deliveryDate = (java.sql.Date) order.get("actualEndDate");
                
                // 设置当前日期为交付日期，确保单号生成正确
                GenUtil.setCurDate(deliveryDate.toLocalDate());
                
                // 查询当天已有的最大入库单号
                String dateStr = deliveryDate.toString().replace("-", "");
                try (PreparedStatement checkStmt = connection.prepareStatement("SELECT repositoryNumber FROM ck03Listing WHERE repositoryNumber LIKE ? ORDER BY repositoryNumber DESC LIMIT 1")) {
                    checkStmt.setString(1, "CK" + dateStr + "%");
                    ResultSet checkRs = checkStmt.executeQuery();
                    if (checkRs.next()) {
                        String maxRepositoryNumber = checkRs.getString("repositoryNumber");
                        try {
                            // 解析单号格式：CK + 日期(YYYYMMDD) + 序号
                            // 例如：CK20250221000110，序号部分是 00110
                            String numberPart = maxRepositoryNumber.substring(10); // 从第11位开始是序号
                            currentInboundNumber = Long.parseLong(numberPart);
                        } catch (NumberFormatException e) {
                            // 忽略无法解析的单号
                        }
                    }
                }
                break; // 只需要查询一次，因为所有生产工单都是同一天的
            }
            
            for (Integer orderId : orderIds) {
                Map<String, Object> order = DbUtil.getById(connection, "missionInformationSc02", orderId);
                if (order == null) continue;
                
                // 获取生产工单的交付日期
                java.sql.Date deliveryDate = (java.sql.Date) order.get("actualEndDate");
                
                // 设置当前日期为交付日期，确保单号生成正确
                GenUtil.setCurDate(deliveryDate.toLocalDate());
                
                // 生成入库单号（按原始逻辑，从最大序号开始递增）
                String dateStr = deliveryDate.toString().replace("-", "");
                String inboundNumber = "CK" + dateStr + String.format("%04d", ++currentInboundNumber);
                
                // 创建入库单
                PreparedStatement statement = connection.prepareStatement("insert into ck03Listing (id,documentType,repositoryNumber,operator,repositoryDate,approvalStatus) values (?,?,?,?,?,?)");
                statement.setInt(1, ++maxId);
                statement.setString(2, "生产入库");
                statement.setString(3, inboundNumber);
                statement.setString(4, order.get("officerincharge").toString());
                statement.setDate(5, deliveryDate);
                statement.setString(6, "已通过");
                statement.execute();

                // 创建入库明细
                statement = connection.prepareStatement("insert into ck04ListOfEntriesListOfEntries(id,productInformation,numberOfEntries,linkToList)values (?,?,?,?)");
                statement.setInt(1, ++maxIdDetail);
                statement.setInt(2, Integer.parseInt(order.get("productName").toString()));
                statement.setBigDecimal(3, new BigDecimal(order.get("numberOfTasks").toString()));
                statement.setInt(4, maxId);
                statement.execute();
                
                inboundCount++;
                System.out.println("正在处理第 " + inboundCount + " 个成品入库，入库单号: " + inboundNumber + ", 日期: " + deliveryDate + ", 生产工单ID: " + orderId);
            }
            
            connection.commit();
        } catch (Exception ex) {
            throw new RuntimeException(ex);
        }
        
        System.out.println("成品入库数据生成完成，共生成 " + orderIds.size() + " 条记录");
    }

    private static int getMaxId(Connection connection, String table) throws SQLException {
        try (Statement stmt = connection.createStatement();
             ResultSet rs = stmt.executeQuery("SELECT MAX(id) FROM " + table)) {
            if (rs.next()) {
                return rs.getInt(1);
            }
        }
        return 0;
    }
    
    private static java.sql.Date getSqlDate(Object dateObj) {
        if (dateObj instanceof Timestamp) {
            return new java.sql.Date(((Timestamp) dateObj).getTime());
        } else if (dateObj instanceof java.sql.Date) {
            return (java.sql.Date) dateObj;
        } else {
            throw new RuntimeException("Unsupported date type: " + dateObj.getClass().getName());
        }
    }
} 